Custom Excel Menus

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

EXCELence

LEVEL: New Programmer Excel | Custom Menus, CommandBars

Menu Management and Best Practices

By Charles Maxson

Many developers often go to great lengths to create sophisticated Excel applications, only to fall short when it comes to providing intuitive ways to use the program. And no, a CommandButton named Click Me floating somewhere on the spreadsheet doesn't make the grade as an acceptable user interface. This is especially true given Excel's natural extensibility; a custom menu is just a few keystrokes away.

This article will describe how to add custom menus to Excel, and discuss some recommended best practices for doing the job correctly. It will also introduce a technique that makes adding custom menus as simple as typing values into a spreadsheet. Which is exactly how we'll do it!

Why Menus?

Before we dive into the topic of how to use menus, let's make sure we really understand why. It's simple; menus are the way most people expect to work with Windows applications. Users are conditioned to look at the menu bar when they want to do something, or are looking for help. Leveraging that user behavior with your applications is an obvious advantage, and reduces training, support, etc. Yes, there are other ways to interact with users - buttons, shortcut keys, etc. - but menus are always a good bet.

Before we go too much further, remember that although we tend to think of the text-based menu bar at the top of an application as "the menu," there are many types of "menus," such as toolbars or context-sensitive menus (sometimes called shortcut or right-click menus) that provide powerful ways for you to enhance user interaction with your application. Since Microsoft lumped them together in a common object named CommandBar, I commonly refer to all instances of these as "menus." So unless specifically stated, I may mean a toolbar and/or traditional menu when I generically say "menu."

Office's Common Objects

One of the nice things that Microsoft did in the more recent versions of Office was to make menu/toolbar functionality more consistent across all Office applications. One of the biggest steps in this improvement was to move all the application-specific VBA commands into a common object library.

For developers, it means only having to learn (and subsequently reference) one type library: the "Microsoft Office 9.0 Object Library" for Office 2000, or the "Microsoft Office 10.0 Object Library" for Office XP. Therefore, once you master objects such as CommandBar and CommandBarControl for, say, Excel, you can apply that knowledge across all Office applications. Because they're relatively straightforward, we won't go deep into the model diagram of the CommandBar object and family of objects.

QUICKtip: Code-free custom menus

You don't need to write code to create custom menus in Excel. Simply use the Customize dialog box, accessible by selecting Tools | Customize, or by right-clicking over any menu and selecting Customize. As you read on, however, you'll see the limitations of this method, and understand why writing code is often a better way to create custom menus for your Excel applications.

The Excel Difference

From a coding standpoint, the shared library that spans all Office applications simplifies working with menus. But for the most part, that's where the consistency ends. For instance, Excel differs from Word in how it supports menus as a host application.

The big difference is the scope of menus. In Word, menus are bound or attached to documents and templates. In other words, when you create a template in Word with a custom menu or toolbar in it, Word manages that customization for you. If you close the template or a document attached to it, Word removes your custom menu. If you [Alt][Tab] to another document that doesn't share your custom menu, Word intelligently removes it without any intervention required from your application. When your document is reactivated, Word automatically recreates your custom menu. Because of this attachment between templates and menus, the Customize dialog box is the preferred method to build custom menus in Word. In fact, I discourage you from coding custom menus in Word - not just because it's easier and faster to use the Customize dialog box, but also for the sake of consistency and to avoid possible errors.

Excel menus are scoped at the application level rather than the document or template level. Because Excel has no document/template paradigm like Word, the entire Excel application is subject to your menus when you create a custom menu in Excel. This introduces many issues for developers to manage so an application doesn't wreak havoc within Excel.

Let's say, for example, that an application runs a routine that builds custom menus. After the workbook is closed, the custom menus will remain unless the application specifically removes them. This is true even if the menus were created as Temporary (a Boolean property of the Add method for the CommandBar object); they aren't removed until Excel itself is closed. Obviously, if menus linger longer than necessary, they can confuse users with options that are no longer relevant, and cause code to be executed in improper situations

.

Responsible Menu Management

The bottom line when using custom menus in Excel is to practice responsible menu management. This is quite easy; once you're in the habit, it will become second nature. The rule of thumb is to clean up after yourself. That is, when your application has control, show your menus. When it doesn't, delete them. Then rebuild them from scratch when your application has control again.

You can do this by leveraging common events of the Workbook object. Put a reference to your menu-building routine in the Open and Activate events of the workbook, so that on the way into your application, the menus will be built when your workbook is opened. In the workbook's BeforeClose and Deactivate events, place a call to the routine that destroys your custom menu(s), when your application isn't in use.

Another pitfall for developers opens when building multiple custom menus. You've probably seen this when an application duplicates its menus incorrectly, and you end up with a menu bar full of duplicates. You can prevent this by simply running code to delete any instance of your custom menus before you attempt to create new ones. If they exist, you'll purge them first, ensuring that you have only one copy of any menu at a time in the application.

The menu removal routine that you run when closing/deactivating your workbook should actually be run at the start of your workbook's Open and Activate events, or any other time you create menus. The following is an example of a routine used to remove a custom menu named "MyMenu" from Excel's worksheet menu bar:

  Sub RemoveMenus()
   On Error Resume Next
  CommandBars("Worksheet Menu Bar").Controls( _
    "MyMenu").Delete
End Sub

Note that the often-discouraged On Error Resume Next command is used here to alleviate the need for extensive error handling. This is one of those exceptions where developers agree that the use of On Error Resume Next is an acceptable practice. The fact that it doesn't trip an error may also lead you to get in the practice of simply including your version of the RemoveMenus routine at the start of the routine that creates your menus.

Another scenario for possible menu management headaches is when you have an application that uses many workbooks. Because you can't put menu code in every instance of each workbook your application uses - of course you wouldn't want to, even if you could! - you need a way to determine which workbooks have access to your menus and which ones don't. This can be accomplished in a number of ways. The one I use most often is to "tag" any workbook that belongs to my application, usually with a custom document property, or a value in a defined name in the workbook. Then I can deny access to my menu items to any workbook without the proper tag.

Menu Creation Made Easy

Enough about managing Excel menus. Let's see how easy it is to create them. We'll walk through a process that uses Excel ranges and some VBA to create menus based on values typed into cells. I struck upon this methodology after finding myself reusing my menu creation routines ad nauseum, repeatedly editing the same block of code. I had to edit the code to make even simple changes, such as changing a menu item name, or button image, or the position of a menu item. Somewhere along the line I decided to replace this hard-coded approach with one that was table-driven - with Excel tables of course.

In this example, we'll target the most common use of custom menus in Excel: adding custom menu items to the menu bar (which is just another CommandBar object) just before the Help item, as shown in FIGURE 1. Begin by setting up a worksheet to hold your custom menu data within your application. It's pretty common in Excel development to have a hidden worksheet in a workbook for holding temporary and persistent data; find some open space on that worksheet and start there. If you don't normally use a "catch all" storage worksheet, add a new blank worksheet to your workbook.

FIGURE 1: Adding custom menu items.

Now you can start to build the menu table shown in FIGURE 2. Across the top row of the table are the property names that are going to be set in the menu: Order, Menu Caption, etc. This row is just to make menu design easier; it isn't used for anything at run time. The first column, A, holds the numeric position for the menu items. It can be used to resort the menu items; simply change the numbers to reflect the new order and use Excel's built-in sort feature to make the change. This will keep you from having to retype menus when the desired order changes later, which always seems to happen.

FIGURE 2: Building a custom menu table.

Here's where it starts to get interesting. Column B contains the Menu Caption details. The first item in the Menu Caption column represents the custom popup menu (CommandBar control type CommandBarPopup), which resides next to Excel's Help menu item. This first item also serves as the starting point for processing the entire table, with all the elements in the Menu Caption column that follow becoming menu items underneath this title menu. A defined name range - called "CustomMenu" in this case, which contains the cell value "&Custom" - is used to identify this starting point from code. The flexibility of defined names allows you to move this table without worrying about absolute cell references.

Everything else is processed relative to its position from the starting cell. Each row of the table represents a new menu item to be added; subsequent columns contain values for other properties to be set on the menu items. Again, referring to FIGURE 2, you can see there are columns that hold the values for the OnAction, BeginGroup, FaceID, State, and Enabled properties for each menu item. The values of these properties are typed into cells of the table using the standard arguments for each specific property. (Make sure the values put into these cells are valid for each property. For example, BeginGroup is either True or False.) Once you have this table set up with your menu metadata, the code executes each line in the table as a menu item until it encounters a blank row, which causes execution to stop. Let's go through the code in detail to see how it all ties together.

One Size Fits All: Reusable Menu Code

As you can see in Listing One, it takes very little code to use the menu table to create custom menus. Best of all, you never have to touch this code again unless you need to manage additional menu properties that aren't captured in the current table, such as TooltipText or Style. The AddCustomMenu routine starts by removing existing custom menus. Remember, you want to make sure you haven't added a duplicate menu, so AddCustomMenu simply calls the RemoveMenus routine as a precaution.

A range reference is then set to determine the starting point for processing the menu:

  Set rngMenu =
Range("CustomMenu") 

The top menu item is created by adding a popup-type control to the CommandBars collection. Because it's always going to be added before Excel's Help menu, the Before property is determined by simply obtaining the count of all controls on the menu bar CommandBar, which forces it to be the next-to-last menu item:

  Set cmbMenu = CommandBars("Worksheet Menu Bar").Controls. _
  Add(Type:=msoControlPopup, Before:=CommandBars( _
  "Worksheet Menu Bar").Controls.Count) 

With the reference to the new menu item, its Caption property is set by taking the value from the initial range reference set above. In this case, the menu created is captioned "Custom". Note that the caption values in the cells of the menu table contain ampersands to set the accelerator keys of each menu item. The following code sets the Caption and DescriptionText properties:

  With cmbMenu
  .Caption = rngMenu.Value
  .DescriptionText = rngMenu.Value & " Menu" 
End With

After the top-level menu is created, the routine enters a DoUntil loop that moves down the Menu Caption column to the next cell, adding a new menu item for each non-blank row it encounters. It does this by resetting the reference to the Range object named rngMenu for each row using the Offset method. Then it evaluates it to see if it's blank. If it isn't, a new control is added to the menu, as follows:

  Set rngMenu = rngMenu.Offset(1, 0) 
Do Until rngMenu.Value = Empty
   Set cmbcMenuItem = _
    cmbMenu.Controls.Add(Type:=msoControlButton) 
  ... 

While still looping on that row inside the table, the property details about each menu item, such as its OnAction and BeginGroup properties, are retained and set. When a blank row is found in the Menu Caption column, the loop will end and the routine ends by freeing the work variables.

Again, to introduce a new menu item to the list, just add a new row to the table; the code will automatically build the menu accordingly the next time it's run. To remove an item, simply delete the row. Either way, you never have to touch the code - just manage the table in the worksheet. All this processing is done with range references and offsets, so the cursor is never physically moved and cells are never referred to by cell addresses. This is much more efficient and flexible in regard to processing time and extensibility, and should always be exercised as a best practice in Excel development.

Now that your custom menu is built, you can also manage it during run time using this table-driven method. For example, you could control the Enabled property of a menu item in your custom menu because the item's availability is dependent upon user inputs. Instead of writing more code and managing it that way, you can simply reference the cell that corresponds to that menu item's Enabled property, change that cell's value to True or False depending on the situation, and rerun the AddCustomMenu routine each time the situation changes. In the end, you'll have a nice separation between application functions and your custom menu code.

Conclusion

Once you unlock the mystery of adding custom menus and toolbars to your Excel applications, you can provide an intuitive, predictable interface for users to access your functionality. By mixing in a little of the natural capabilities of Excel to provide a storage mechanism for menu data, you can create reusable, flexible code that you never need revisit. Add that to some thoughtful, responsible menu management techniques, and you'll boost the value of your Excel applications with minimal effort.

Charles Maxson has spent a long time focusing on building applications with Microsoft Office. In his role as a Technical Evangelist for Plural, Inc., Charles likes to emphasize the importance of Office as a development platform, whether it's part of the solution or the entire solution. He frequently speaks at conferences and writes about Office hoping to share his experiences with others. He willingly admits his two favorite products are Excel and Word, but agrees that Microsoft Office as a whole has an unlimited potential to help solve nearly any problem. You can reach Charles at mailto:maxsonc@plural.com for his Office-centric opinion.

Begin Listing One - AddCustomMenu

  Option Explicit
  
' Iterates through the Custom Menu table
' creating a menu next to Excel's Help menu. 
Sub AddCustomMenu()
   Dim cmbMenu As CommandBarPopup
   Dim cmbcMenuItem As CommandBarControl
   Dim rngMenu As Range
  
   ' Ensure menu doesn't already exist. 
  RemoveMenus
   ' Designate start of menu range. 
   Set rngMenu = Range("CustomMenu") 
   ' Add a new popup menu bar, set it to cmbMenu. 
   Set cmbMenu = CommandBars("Worksheet Menu Bar"). _
    Controls.Add(Type:=msoControlPopup, _
    Before:=CommandBars("Worksheet Menu Bar") _
    .Controls.Count) 
   ' Set the caption of the new menu. 
   With cmbMenu
    .Caption = rngMenu.Value
    .DescriptionText = rngMenu.Value &
" Menu" 
   End With
   ' *Move* down to the next row of the menu range. 
   Set rngMenu = rngMenu.Offset(1, 0) 
   ' Loop until the range is blank. 
   Do Until rngMenu.Value = Empty
     ' Add a new menu bar button, set it to cmbcMenuItem. 
     Set cmbcMenuItem = _
      cmbMenu.Controls.Add(Type:=msoControlButton) 
     ' Set the properties of the button found in menu range. 
     With cmbcMenuItem
      .Caption = rngMenu.Value
      .OnAction = rngMenu.Offset(0, 1).Value
      .BeginGroup = rngMenu.Offset(0, 2).Value
       If IsNumeric(rngMenu.Offset(0, 3).Value) Then
        .FaceId = rngMenu.Offset(0, 3).Value
       ElseIf rngMenu.Offset(0, 3).Value <> "" Then
        ThisWorkbook.Worksheets("wsWorking").Shapes _
           (rngMenu.Offset(0, 3).Value).Copy
        .PasteFace
       End If
      .State = rngMenu.Offset(0, 4).Value
      .Enabled = rngMenu.Offset(0, 5).Value
     End With
     ' *Move* down to the next row of the menu range. 
     Set rngMenu = rngMenu.Offset(1, 0) 
   Loop
   ' Release variables. 
   Set rngMenu = Nothing
   Set cmbcMenuItem = Nothing
   Set cmbMenu = Nothing
End Sub
  
' Remove custom menu defined in CustomMenu range.
Sub RemoveMenus()
   On Error Resume Next
   ' Remove Menu Bar. 
  CommandBars("Worksheet Menu Bar").Controls( _
    ThisWorkbook.Worksheets("wsWorking").Range( _
    "CustomMenu").Value).Delete
End Sub
Option Explicit
  
' Iterates through the Custom Menu table
' creating a menu next to Excel's Help menu. 
Sub AddCustomMenu()
   Dim cmbMenu As CommandBarPopup
   Dim cmbcMenuItem As CommandBarControl
   Dim rngMenu As Range
  
   ' Ensure menu doesn't already exist. 
  RemoveMenus
   ' Designate start of menu range. 
   Set rngMenu = Range("CustomMenu") 
   ' Add a new popup menu bar, set it to cmbMenu. 
   Set cmbMenu = CommandBars("Worksheet Menu Bar"). _
    Controls.Add(Type:=msoControlPopup, _
    Before:=CommandBars("Worksheet Menu Bar") _
    .Controls.Count) 
   ' Set the caption of the new menu. 
   With cmbMenu
    .Caption = rngMenu.Value
    .DescriptionText = rngMenu.Value & " Menu" 
   End With
   ' *Move* down to the next row of the menu range. 
   Set rngMenu = rngMenu.Offset(1, 0) 
   ' Loop until the range is blank. 
   Do Until rngMenu.Value = Empty
     ' Add a new menu bar button, set it to cmbcMenuItem. 
     Set cmbcMenuItem = _
      cmbMenu.Controls.Add(Type:=msoControlButton) 
     ' Set the properties of the button found in menu range. 
     With cmbcMenuItem
      .Caption = rngMenu.Value
      .OnAction = rngMenu.Offset(0, 1).Value
      .BeginGroup = rngMenu.Offset(0, 2).Value
       If IsNumeric(rngMenu.Offset(0, 3).Value) Then
        .FaceId = rngMenu.Offset(0, 3).Value
       ElseIf rngMenu.Offset(0, 3).Value <> "" Then
        ThisWorkbook.Worksheets("wsWorking").Shapes _
           (rngMenu.Offset(0, 3).Value).Copy
        .PasteFace
       End If
      .State = rngMenu.Offset(0, 4).Value
      .Enabled = rngMenu.Offset(0, 5).Value
     End With
     ' *Move* down to the next row of the menu range. 
     Set rngMenu = rngMenu.Offset(1, 0) 
   Loop
   ' Release variables. 
   Set rngMenu = Nothing
   Set cmbcMenuItem = Nothing
   Set cmbMenu = Nothing
End Sub
  
' Remove custom menu defined in CustomMenu range.
Sub RemoveMenus()
   On Error Resume Next
   ' Remove Menu Bar. 
  CommandBars("Worksheet Menu Bar").Controls( _
    ThisWorkbook.Worksheets("wsWorking").Range( _
    "CustomMenu").Value).Delete
End Sub

End Listing One